package persistencia;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import modelo.Vaga;

public class DAOVaga {
   
	    private Connection conexao;
	    
	    private void obterConexao(){
	        try {
	            Class.forName("com.mysql.jdbc.Driver");
	            conexao = DriverManager.getConnection("jdbc:mysql://localhost/sirp", "root", "123");
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    
	    private void liberarConexao(){
	        try {
	            conexao.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }    
	    
	    public void inserir(Vaga vaga){
	        obterConexao();
	        try {
	            PreparedStatement pstat = conexao.prepareStatement("INSERT INTO Vaga (tipo, cargo, experienciaExigida, descricao, dataVaga, salario) VALUES (?, ?, ?, ?, ?, ?)");
	            pstat.setInt(1, vaga.getId());
	            pstat.setString(2, vaga.getTipo());
	            pstat.setString(3, vaga.getCargo());
	            pstat.setString(4, vaga.getExperienciaExigida());
	            pstat.setString(5, vaga.getDescricao());
	            pstat.setString(6, vaga.getDataVaga());
	            pstat.setString(7, vaga.getSalario());
	            
	            pstat.executeUpdate();
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        liberarConexao();
	    }
	    
	    public void atualizar(Vaga vaga){
	        obterConexao();
	        try {
	            PreparedStatement stat = conexao.prepareStatement("UPDATE Vaga SET tipo = ?, cargo = ?, experienciaExigida = ?, descricao = ?, dataVaga = ?, salario = ? WHERE id = ? ");
	            stat.setInt(1, vaga.getId());
	            stat.setString(2, vaga.getTipo());
	            stat.setString(3, vaga.getCargo());
	            stat.setString(4, vaga.getExperienciaExigida());
	            stat.setString(5, vaga.getDescricao());
	            stat.setString(6, vaga.getDataVaga());
	            stat.setString(7, vaga.getSalario());
	            
	            stat.executeUpdate();
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        liberarConexao();
	    }
	    
	    public void excluir(Vaga vaga){
	        obterConexao();
	        try {
	            Statement stat = conexao.createStatement();
	            stat.executeUpdate("DELETE FROM Vaga WHERE id = '" + vaga.getId() + "' ");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        liberarConexao();
	    }
	    
	    public Vaga buscarVaga(int i){
	        obterConexao();
	        try {
	            PreparedStatement stat = conexao.prepareStatement("SELECT * FROM Vaga WHERE id = ?");
	            stat.setInt(1, i);
	            ResultSet set = stat.executeQuery();
	            
	            
	            if (set.next()){
	            	Vaga vaga = new Vaga();
	            	vaga.setId(set.getInt("id"));
	            	vaga.setTipo(set.getString("tipo"));
	            	vaga.setCargo(set.getString("cargo"));
	            	vaga.setDescricao(set.getString("descricao"));
	            	vaga.setExperienciaExigida(set.getString("experienciaExigida"));
	            	vaga.setDataVaga(set.getString("dataVaga"));
	            	vaga.setSalario(set.getString("salario"));
	                stat = conexao.prepareStatement("SELECT count(*) FROM Vaga WHERE vaga_fk = ?");
	                stat.setInt(1, i);
	                set = stat.executeQuery();
	                
	                return vaga;
	            }
               	            
	            
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        liberarConexao();
	        return null;
	    }
	    
	
}
